﻿using System;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Core;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using DTSExecResult = Microsoft.SqlServer.Dts.Runtime.DTSExecResult;
using DTSProductLevel = Microsoft.SqlServer.Dts.Runtime.DTSProductLevel;
using Excel = Microsoft.Office.Interop.Excel;
using VariableDispenser = Microsoft.SqlServer.Dts.Runtime.VariableDispenser;

namespace oh22is.SqlServer
{
    [DtsTask(
        Description = "Refresh Microsoft Excel Task",
        DisplayName = "Refresh Microsoft Excel Task",
        IconResource = "oh22is.SqlServer.PQRefresh.ico",
        UITypeName = "oh22is.SqlServer.PqRefreshUi, oh22is.SqlServer.PQRefersh, Version=1.0.0.0,Culture=neutral,PublicKeyToken=4983f182acb1fc6b",
        TaskContact = "oh22information services GmbH",
        RequiredProductLevel = DTSProductLevel.None
        )]
    public class PqRefreshTask : Task
    {


        private double _factor = 10;
        private bool _fireAgain = false;
        private int _timeout = 10000;

        /// <summary>
        /// 
        /// </summary>
        public string ConnectionManager { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public int Timeout
        {
            set
            {
                _timeout = value;
            }
            get
            {
                return _timeout;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public double Factor {
            set
            {
                _factor = value;
            }
            get
            {
                return _factor;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public bool DynamicTimeoutCalculation { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public string QueryConnections { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public bool IgnoreTimoutWarning { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public bool RefreshAll { set; get; }

        public bool CreateBackup { set; get; }
    
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connections"></param>
        /// <param name="variableDispenser"></param>
        /// <param name="componentEvents"></param>
        /// <param name="log"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
        {
            var cm = connections[ConnectionManager];
            var file = new FileInfo(cm.Properties["ExcelFilePath"].GetValue(cm).ToString());

            if (CreateBackup && !String.IsNullOrEmpty(file.DirectoryName))
            {
                var newFileName = String.Format("{0}_{1}", DateTime.Now.ToString("yyyyMMddhhmmss"), file.Name);
                file.CopyTo(Path.Combine(file.DirectoryName, newFileName));
            }

            var dynamicTimeout = Convert.ToInt32((file.Length * 0.001) * Factor);
            if (DynamicTimeoutCalculation)
            {
                Timeout = dynamicTimeout;
                componentEvents.FireInformation(0, "Excel Refresh", "Dynamic timeout calculation is activated.", null, 0, ref _fireAgain);
                componentEvents.FireInformation(0, "Excel Refresh", String.Format("Current file size is {0} kB", (file.Length/1000)), null, 0, ref _fireAgain);
            }
            
            Excel.Application excelApp = new Excel.Application
            {
                Visible = false,
                DisplayAlerts = false,
                AskToUpdateLinks = false,
                AlertBeforeOverwriting = false,
                DefaultFilePath = file.DirectoryName
            };

            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(file.FullName,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);

            componentEvents.FireInformation(0, "Excel Refresh", "Excel file has been opened.", null, 0, ref _fireAgain);

            Excel.Connections workbookConnections = excelWorkbook.Connections;

            try
            {

                if (RefreshAll)
                {
                    //SetBackgroundQuery(excelWorkbook, false, componentEvents);
                    componentEvents.FireInformation(0, "Excel Refresh", "Excel Refresh all objects was started.", null, 0, ref _fireAgain);
                    excelWorkbook.RefreshAll();
                }
                else
                {
                    var connection = QueryConnections.Split('|');

                    foreach (Excel.WorkbookConnection cnn in workbookConnections)
                    {
                        Excel.WorkbookConnection cnn1;
                        switch (cnn.Type)
                        {
                            case Excel.XlConnectionType.xlConnectionTypeDATAFEED:
                                componentEvents.FireWarning(0, "Excel Refresh", "DATAFEED connection not yet implemented.", null, 0);
                                break;

                            case Excel.XlConnectionType.xlConnectionTypeMODEL:
                                componentEvents.FireWarning(0, "Excel Refresh", "MODEL connection not yet implemented.", null, 0);
                                break;

                            case Excel.XlConnectionType.xlConnectionTypeNOSOURCE:
                                componentEvents.FireWarning(0, "Excel Refresh", "NO SOURCE", null, 0);
                                break;

                            case Excel.XlConnectionType.xlConnectionTypeODBC:
                                cnn1 = cnn;
                                foreach (var con in connection.Where(con => con == cnn1.ODBCConnection.CommandText.ToString()))
                                {
                                    componentEvents.FireInformation(0, "Excel Refresh", String.Format("Refreshes the query \"{0}\"...", cnn1.ODBCConnection.CommandText.ToString()), null, 0, ref _fireAgain);
                                    cnn1.ODBCConnection.BackgroundQuery = false;
                                    cnn1.ODBCConnection.Application.CalculateUntilAsyncQueriesDone();
                                    cnn1.ODBCConnection.Refresh();
                                    while (cnn1.ODBCConnection.Refreshing || cnn1.ODBCConnection.Application.CalculationState != Excel.XlCalculationState.xlDone)
                                    {
                                        componentEvents.FireInformation(0, "Excel Refresh", String.Format("Refreshes the query \"{0}\"...", cnn1.ODBCConnection.CommandText.ToString()), null, 0, ref _fireAgain);
                                    }
                                }
                                break;

                            case Excel.XlConnectionType.xlConnectionTypeOLEDB:
                                cnn1 = cnn;
                                foreach (var con in connection.Where(con => con == cnn.OLEDBConnection.CommandText.ToString()))
                                {
                                    componentEvents.FireInformation(0, "Excel Refresh", String.Format("Refreshes the query \"{0}\"...", cnn1.OLEDBConnection.CommandText.ToString()), null, 0, ref _fireAgain);
                                    cnn1.OLEDBConnection.BackgroundQuery = false;
                                    cnn1.OLEDBConnection.Application.CalculateUntilAsyncQueriesDone();
                                    cnn1.OLEDBConnection.Refresh();
                                    while (cnn1.OLEDBConnection.Refreshing || cnn1.OLEDBConnection.Application.CalculationState != Excel.XlCalculationState.xlDone)
                                    {
                                        componentEvents.FireInformation(0, "Excel Refresh", String.Format("Refreshes the query \"{0}\"...", cnn1.OLEDBConnection.CommandText.ToString()), null, 0, ref _fireAgain);
                                    }
                                }
                                break;

                            case Excel.XlConnectionType.xlConnectionTypeTEXT:
                                componentEvents.FireWarning(0, "Excel Refresh", "TEXT connection not yet implemented.", null, 0);
                                break;

                            case Excel.XlConnectionType.xlConnectionTypeWEB:
                                componentEvents.FireWarning(0, "Excel Refresh", "WEB connection not yet implemented.", null, 0);
                                break;

                            case Excel.XlConnectionType.xlConnectionTypeWORKSHEET:
                                componentEvents.FireWarning(0, "Excel Refresh", "WORKSHEET connection not yet implemented.", null, 0);
                                break;

                            case Excel.XlConnectionType.xlConnectionTypeXMLMAP:
                                componentEvents.FireWarning(0, "Excel Refresh", "XMLMAP connection not yet implemented.", null, 0);
                                break;
                        }
                    }
                }

                componentEvents.FireInformation(0, "Excel Refresh", String.Format("Refresh timeout of {0} milliseconds is started...", Timeout), null, 0, ref _fireAgain);
                System.Threading.Thread.Sleep(Timeout);
                componentEvents.FireInformation(0, "Excel Refresh", "Refresh timeout finished.", null, 0, ref _fireAgain);
                
                //SetBackgroundQuery(excelWorkbook, true, componentEvents);

                excelWorkbook.Save();
                componentEvents.FireInformation(0, "Excel Refresh", "Excel has been saved.", null, 0, ref _fireAgain);
            }
            catch (Exception ex)
            {
                componentEvents.FireError(0, "Excel Refresh", "Failed to refresh the Excel file.", "", 0);
                componentEvents.FireError(0, "Excel Refresh", ex.ToString(), "", 0);
            }
            finally
            {
                //excelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
                excelWorkbook.Close(0);
                excelApp.Quit();
                Marshal.FinalReleaseComObject(workbookConnections);
                Marshal.FinalReleaseComObject(excelWorkbook);
                Marshal.FinalReleaseComObject(excelApp);
                excelApp = null;
            }

            return base.Execute(connections, variableDispenser, componentEvents, log, transaction);

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="backgroundQuery"></param>
        /// <param name="componentEvents"></param>
        private void SetBackgroundQuery(Excel.Workbook workbook, bool backgroundQuery, IDTSComponentEvents componentEvents)
        {
            foreach (Excel.Worksheet wk in workbook.Worksheets)
            {
                foreach (Excel.QueryTable qt in wk.QueryTables)
                {
                    qt.BackgroundQuery = backgroundQuery;
                }
            }

            foreach (Excel.PivotTable pv in workbook.PivotTables)
            {
                pv.PivotCache().BackgroundQuery = backgroundQuery;
            }

            foreach (Excel.PivotCache pc in workbook.PivotCaches())
            {
                pc.BackgroundQuery = backgroundQuery;
            }

            foreach (Excel.WorkbookConnection cnn in workbook.Connections)
            {

                if (cnn.Type.ToString() == "xlConnectionTypeODBC")
                {
                    cnn.ODBCConnection.BackgroundQuery = backgroundQuery;
                }
                else
                {
                    cnn.OLEDBConnection.BackgroundQuery = backgroundQuery;
                }
            }

            componentEvents.FireInformation(0, "Excel Refresh", String.Format("BackgroundQuery for different objects was set to {0}.",backgroundQuery.ToString()), null, 0, ref _fireAgain);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connections"></param>
        /// <param name="variableDispenser"></param>
        /// <param name="componentEvents"></param>
        /// <param name="log"></param>
        /// <returns></returns>
        public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
        {

            if (String.IsNullOrEmpty(ConnectionManager))
            {
                componentEvents.FireError(0, "Excel Refresh", "A ConnectionManager must be defined.", null, 0);
            }

            if (Timeout <= 0 && DynamicTimeoutCalculation == false)
            {
                componentEvents.FireError(0, "Excel Refresh", "Timeout must be greater than 0.", null, 0);
            }

            //if (Timeout == 0 && String.IsNullOrEmpty(QueryConnections) && DynamicTimeoutCalculation == false)
            //{
            //    componentEvents.FireError(0, "Excel Refresh", "You must select at least an QueryConnection if timeout is 0.", null, 0);
            //}

            if (DynamicTimeoutCalculation && !IgnoreTimoutWarning)
            {
                componentEvents.FireWarning(0, "Excel Refresh", "Dynamic timeout calculation ensures not, that all data have been refreshed.", null, 0);
            }

            if (!DynamicTimeoutCalculation && !IgnoreTimoutWarning)
            {
                componentEvents.FireWarning(0, "Excel Refresh", "Specifying a timeout ensures not, that all data have been refreshed.", null, 0);
            }

            return base.Validate(connections, variableDispenser, componentEvents, log);
        }

    }
}
                                  